package com.opennetexam.manager.mapper;

import com.opennetexam.manager.databasemodel.ExamSubject;
import com.opennetexam.manager.dto.ExamineePlanInfo;
import com.opennetexam.manager.dto.ExaminerSubjectInfoDTO;
import com.opennetexam.manager.sqlprovider.ExamSubjectProvider;
import org.apache.ibatis.annotations.*;

import java.util.List;
import java.util.Map;

/**
 * @author 奶茶
 * Created on 2017/11/14.
 */
@Mapper
public interface ExamSubjectMapper {

    @SelectProvider(type = ExamSubjectProvider.class,method = "examSubjectFirstOrDefault")
    ExamSubject examSubjectFirstOrDefault(@Param(value = "appId") String appId, @Param(value = "examPlanInfoID") String examPlanInfoID, @Param(value = "examSubjectID") String examSubjectID);

    @SelectProvider(type = ExamSubjectProvider.class,method = "examPaperIsShuffled")
    ExamSubject examPaperIsShuffled(@Param(value = "examSubjectID") String examSubjectID);

    @Select("SELECT ExamPlanInfoID,ExamSubjectID,SubjectCode,SubjectName FROM ExamSubject WHERE AppID = #{appID} AND ExamPlanInfoID = #{examPlanID} AND SubjectPaperStatus != #{subjectPaperStatus}")
    List<ExamSubject> findSubjectList(@Param(value = "appID") String appID, @Param(value = "examPlanID") String examPlanID, @Param(value = "subjectPaperStatus") Integer subjectPaperStatus);

    @Select("SELECT AppID,ExamPlanInfoID,ExamSubjectID,SubjectCode,SubjectName,SubjectPaperStatus FROM ExamSubject WHERE ExamPlanInfoID = #{examPlanID}")
    List<ExamSubject> findAllExamSubjectByExamPlanID(@Param(value = "examPlanID") String examPlanID);

    @Select("SELECT\n" +
            "\tSubjectPaperStatus,\n" +
            "\tCOUNT(1) Count,\n" +
            "\t(SELECT COUNT(1) FROM ExamSubject WHERE ExamPlanInfoID = #{examPlanID}) allCount\n" +
            "FROM\n" +
            "\tExamSubject\n" +
            "WHERE\n" +
            "\tExamPlanInfoID = #{examPlanID}\n" +
            "GROUP BY\n" +
            "\tSubjectPaperStatus")
    List<Map<String,Object>> findExamPlanSubjectCount(@Param(value = "examPlanID") String examPlanID);

    @Select("SELECT\n" +
            "\tCOUNT(1) count,\n" +
            "\tsubjectPaperStatus,\n" +
            "(\n" +
            "\t\tSELECT\n" +
            "\t\t\tCOUNT(1)\n" +
            "\t\tFROM\n" +
            "\t\t\tExamSubject\n" +
            "\t\tWHERE\n" +
            "\t\t\tExamPlanInfoID = #{examPlanID}\n" +
            "\t) AS allCount\n" +
            "FROM\n" +
            "\t`ExamSubject`\n" +
            "WHERE\n" +
            "\tExamPlanInfoID = #{examPlanID}\n" +
            "GROUP BY SubjectPaperStatus")
    List<Map<String,Object>> findExamSubjectFailCount(@Param(value = "examPlanID") String examPlanID);

    @Select("SELECT COUNT(1) FROM ExamSubject WHERE ExamPlanInfoID = #{examPlanID}")
    Integer findAllExamSubjectCount(@Param(value = "examPlanID") String examPlanID);

    @Select("SELECT COUNT(1) FROM ExamSubject WHERE ExamPlanInfoID = #{examPlanID} AND SubjectPaperStatus = #{subjectStatus}")
    Integer findSubjectCountBYStatus(@Param(value = "examPlanID") String examPlanID,@Param(value = "subjectStatus") Integer subjectStatus);

    //@SelectProvider(type = ExamSubjectProvider.class,method = "examSubjectMoniter")
    @Select("SELECT * FROM examsubject WHERE ExamPlanInfoID = #{examPlanInfoID}")
    List<ExamSubject> examSubjectMoniter(@Param(value = "appID") String appID, @Param(value = "examPlanInfoID") String examPlanInfoID, @Param(value = "examSceneID") String examSceneID, @Param(value = "examRoomID") String examRoomID);

    //@SelectProvider(type = ExamSubjectProvider.class,method = "findExamSubjectListBySomeWhere")
    @Select("SELECT * FROM examsubject WHERE ExamPlanInfoID = #{planId}")
    List<ExamSubject> findExamSubjectListBySomeWhere(@Param(value = "planId") String planId);

    @Insert("INSERT INTO ExamSubject (\n" +
            "\tAppID,ExamPlanInfoID,ExamSubjectID,SubjectCode,SubjectName,PaperCreateMode,PaperShuffled,TimeLimits,PreviewPaperTime,PaperShowMode,ExamNotice,RetryLimits,AutoCheckMode,ExamMode,SubjectType,SubjectPaperStatus\n" +
            ") SELECT #{appID},#{examPlanInfoID},#{examSubjectID},#{subjectCode},#{subjectName},#{paperCreateMode},#{paperShuffled},#{timeLimits},#{previewPaperTime},#{paperShowMode},#{examNotice},#{retryLimits},#{autoCheckMode},#{examMode},#{subjectType},#{subjectPaperStatus}\n" +
            "FROM\n" +
            "\tDUAL\n" +
            "WHERE\n" +
            "\tNOT EXISTS (\n" +
            "\t\tSELECT\n" +
            "\t\t\t1\n" +
            "\t\tFROM\n" +
            "\t\t\tExamSubject\n" +
            "\t\tWHERE\n" +
            "\t\t\tExamPlanInfoID = #{examPlanInfoID}\n" +
            "\t\tAND ExamSubjectID = #{examSubjectID}\n" +
            "\t\tLIMIT 1\n" +
            ")")
    Integer insertExamSubject(ExamSubject examSubject);

    @Update("UPDATE ExamSubject SET SubjectCode = #{subjectCode},SubjectName = #{subjectName},PaperCreateMode = #{paperCreateMode},PaperShuffled = #{paperShuffled},TimeLimits = #{timeLimits},PreviewPaperTime = #{previewPaperTime},PaperShowMode = #{paperShowMode},ExamNotice = #{examNotice},RetryLimits = #{retryLimits},AutoCheckMode = #{autoCheckMode},ExamMode = #{examMode},SubjectType = #{subjectType} WHERE AppID = #{appID} AND ExamPlanInfoID = #{examPlanInfoID} AND ExamSubjectID = #{examSubjectID}")
    Integer updateExamSubject(ExamSubject examSubject);

    @Update("UPDATE ExamSubject SET SubjectPaperStatus = #{subjectPaperStatus} WHERE ExamPlanInfoID = #{examPlanInfoID} AND ExamSubjectID = #{examSubjectID}")
    Integer updateExamSubjectPaperStatus(ExamSubject examSubject);

    @Delete("DELETE es FROM ExamSubject es \n" +
            "INNER JOIN ExamineeArrange ea \n" +
            "ON es.ExamSubjectID = ea.ExamSubjectID \n" +
            "WHERE ea.AppID = #{appID} AND ea.ExamPlanInfoID = #{planId} AND es.ExamPlanInfoID = #{planId}")
    void delExamSubjectBySubjectIds(@Param("appID") String appID, @Param("planId") String planId);

    @Select("SELECT\n" +
            "\tExamPlanName,\n" +
            "\tSubjectName\n" +
            "FROM\n" +
            "\texamsubject\n" +
            "JOIN examplaninfo plan ON plan.ExamPlanInfoID = examsubject.ExamPlanInfoID\n" +
            "WHERE\n" +
            "\texamsubject.ExamPlanInfoID = #{examPlanID}\n" +
            "AND examsubject.SubjectPaperStatus = 3")
    List<Map<String,Object>> findFailDownloadSubjectInfo(@Param(value = "examPlanID") String examPlanID);

    @Select("SELECT\n" +
            "\ta.AppID,\n" +
            "\ta.AppName,\n" +
            "\ts.ExamPlanInfoID,\n" +
            "\ts.ExamSubjectID,\n" +
            "\ts.SubjectName examSubjectName,\n" +
            "\tm.SysState,\n" +
            "\tm.ExamineeID\n" +
            "FROM\n" +
            "examapp as a\n" +
            "JOIN examsubject AS s ON a.AppID = s.AppID\n" +
            "JOIN examingmonitor AS m ON m.AppID = s.AppID AND m.ExamPlanInfoID = s.ExamPlanInfoID\n" +
            "AND m.ExamSubjectID = s.ExamSubjectID\n" +
            "WHERE\n" +
            "\tm.ExamineeID = #{examinerID}\n" +
            "AND m.ExamPlanInfoID = #{examPlanID}\n" +
            "AND m.SysState != 3\n" +
            "AND m.SubmitType = 0")
    List<ExamineePlanInfo> getExaminerSubjectList(@Param(value = "examPlanID") String examPlanID, @Param(value = "examinerID") String examinerID);

    @Select("SELECT\n" +
            "\tdataArrange.ExamineeArrangeID,\n" +
            "\tdataArrange.ExamPaperID,\n" +
            "\tdataArrange.ExamPlanInfoID,\n" +
            "\tdataArrange.ExamSceneID,\n" +
            "\tdataArrange.ExamRoomID,dataArrange.PlanBeginTime,\n" +
            "\tdataSubject.ExamSubjectID,dataSubject.SubjectName,dataSubject.TimeLimits,\n" +
            "\tdataSubject.SubjectCode\n" +
            "FROM\n" +
            "\texamineearrange dataArrange\n" +
            "JOIN examingmonitor dataMonitor ON dataMonitor.ExamPlanInfoID = dataArrange.ExamPlanInfoID\n" +
            "AND dataMonitor.ExamSubjectID = dataArrange.ExamSubjectID\n" +
            "AND dataMonitor.ExamineeID = dataArrange.ExamineeID\n" +
            "JOIN examsubject dataSubject ON dataSubject.ExamPlanInfoID = dataMonitor.ExamPlanInfoID\n" +
            "AND dataSubject.ExamSubjectID = dataMonitor.ExamSubjectID\n" +
            "WHERE\n" +
            "\tdataArrange.ExamPlanInfoID = #{examPlanID}\n" +
            "AND dataArrange.ExamSubjectID = #{subjectID}\n" +
            "AND dataArrange.ExamineeID = #{examinerID}\n" +
            "AND dataArrange.PlanBeginTime < NOW()\n" +
            "AND dataArrange.PlanEndTime > NOW()\n" +
            "AND dataMonitor.SysState != 3\n" +
            "AND dataMonitor.SubmitType = 0\n" +
            "LIMIT 1")
    ExaminerSubjectInfoDTO getExaminerSubjectInfo(@Param(value = "examPlanID") String examPlanID, @Param(value = "subjectID") String subjectID, @Param(value = "examinerID") String examinerID);

    @Select("SELECT ExamSubjectID FROM examsubject WHERE ExamPlanInfoID = #{examPlanID} AND SubjectPaperStatus = 2")
    List<String> findAllExamSubjectIDBYExamPlanID(@Param(value = "examPlanID") String examPlanID);
}
